File Handling for All

Appendix B:
Filer Database Management System

The Filer database system is a file management program (originally published in instalments in BEEBUG magazine), with comprehensive facilities for creating and updating databases, and searching for information previously entered. This program also provides powerful print formats, allowing selected data to be printed in any format determined by the user. In addition, a separate sort program provides fast RAM-based sorting of data files. Filer is command driven for speed and efficiency, rather than using the more common menu system. These programs together with a top-level menu are included on the disc associated with this book to provide a complete working database described here.

The program follows very much the principles outlined in chapter 3. Data files follow a fixed field length, fixed record length format with a File Description Record [FDR] at the start of each file. A particular feature is the use of format files to allow for any output layout desired by the user, rather than providing a limited set of pre-determined layouts. A format file, in this context, is a one-record file created and processed as a standard data file by Filer, which contains a description of the output layout to be used.

The commands recognised by Filer are described below.

CREATE

There are two separate forms of the CREATE command: The former is used to create a data file ready for use. You will be prompted to enter: The size of a file is limited by the amount of space on your disc. The maximum number of records will be determined by the size of each record. Each record is limited to a maximum of 12 fields, and each field name is limited to a maximum of 12 characters, and the width of each field is limited to 64 characters.

The second form of the CREATE command is used to create a format file for printing where the value of n is the number of lines that the print form is to contain. A form is limited to a maximum of 11 lines. A format file of one record will then be automatically created.

Both data files and format files may be given any names chosen by the user. Once created, records are processed in all cases using the ADD, UPDATE or DELETE commands.

OPEN

This command is used to open any file for use by Filer, taking the form: Any data file already open will be closed first without any loss of data.

CLOSE

This command will close any data file currently open, at the same time removing any records which have been logically 'deleted' while the file was open.

SHOW

This command will display information (from the FDR) about the current file or a named file on the screen. There are two versions: If the file specified is not the currently open file, then it will be opened as the current file, and any file already open will be closed. If no file name is specified, then details of the current file will be shown.

RENAME

This allows the name of any field in a file (but no other details) to be changed as required. It takes the form: The file must already be open, and 'field1' will be renamed as 'field2'. The SHOW command may be used to check field names before and after renaming. Be careful not to rename a field with a name that already exists, as confusion is likely to arise in the use of other Filer facilities and programs. Should this situation arise, a further use of RENAME will be applied to the first occurrence of a field name in the list of fields.

EXTEND

This command is of the form: and may be used to extend the length of any data file. The command will prompt for the new maximum length of file in records. The file will be extended to the new length without loss of data if this is possible.

ADD

This command requires no arguments and will add (append) new records to a data file. The program will display a blank record on the screen (with field names) and the data is entered into each field in turn. Pressing Return terminates each field. During data entry, the left and right cursor keys will operate to allow data to be edited. Function keys f1 and f2 can be used to insert or delete a space at the cursor position. Once Return has been pressed it is not possible to go back to a previous data field. When all fields have been dealt with, you have the option to confirm (or not) the details entered. If there is a mistake, it will usually be quicker to accept the record and then amend it using the UPDATE option.

The command will repeatedly prompt for the next record to enter. Terminate this process by pressing Escape.

UPDATE

This has the format: where <n> is the number of the record to be amended. The record selected is then displayed on the screen and may be edited field by field as described for the ADD command.

DELETE

This has a similar format: and deletes record number <n>. The selected record is displayed on the screen and you must confirm (or not) that the record is to be deleted. In fact, the record in the file is merely replaced by a blank record (which you can check by displaying the record after deletion). All such 'deleted' records are removed from the file when it is closed (using CLOSE or END).

SELECT

This command takes the form: and is used to select which records will be displayed or printed. The search string should be any expression which, for a given record, will be true or false. It should reference any data fields by field name together with any logical operators and appropriate Basic functions. After specifying a search string, you can return to the initial state by entering:

DISPLAY

This can take one of three forms: In the first case, all the records in the file will be displayed in turn starting with the first. In the second case, only the specified record will be displayed. Lastly, records from record n1 up to and including n2 will be displayed. In addition, records will only be displayed if they match any search string currently in effect (see SELECT command). Records will be in the same standard format as used with the ADD, UPDATE and DELETE commands. Use Escape at any time to terminate the display of records.

FORMAT

The purpose of the FORMAT command is to select a print format for use with the PRINT command. The print format file should already have been created with the special form of the CREATE command (see CREATE above), and the format entered with the usual ADD, UPDATE and DELETE commands. Print formats are described below under the heading of 'Print Formats'. The FORMAT command is specified as: where filename is the name of a format file. The format file is opened, the details read, and the file dosed independently of any data file that may be already open.

PRINT

Again, the same three formats are possible as with the DISPLAY command: Provided that your printer has been switched on and correctly initialised as appropriate, records will be printed in the format determined by the current print format (see FORMAT command). Printing can also be directed towards the screen alone if *FX5,0 is executed first, useful for checking the correctness of a print format. Records will again be printed only if they match any search string in operation.

MARK

For mail-merge operations this can be used to specify a different marker character (@ is the default). The format of this command is:

MERGE

This command is used to specify a text file which will be printed with data from data records inserted in place of previously inserted markers. The text file should be essentially in ASCII format and may be prepared with View, Wordwise, Edit or any other suitable word processor or editor.

Up to nine different fields may be used to replace markers in the text file represented as @1 to @9 (or any other character in place of @ - see MARK command). In operation, all occurrences of @1 are replaced by the contents of the first data field, @2 by the second data field and so on. Each record will result in another customised copy of the text file being sent to the printer.

COMMANDS

This command will display a list of all valid commands on the screen.

END

This command is the only safe and reliable way of exiting from the program. Any data file(s) open at the time will be properly closed.

Escape

The Escape key can be used at any time to abort the current command.

* COMMANDS

Filer will also allow entry of any relevant * commands, allowing a printer to be initialised or a disc to be catalogued, for example, after you have started file processing. Be careful with any commands that may corrupt memory.

PRINT FORMATS

The concept of a print format is as follows. All records to be printed are assumed to be printed on a 'form' and it is the description of the form that appears in the format file. Forms consist of up to 11 lines, any of which may be blank, contain literal text, or the contents of data fields. Each form has a form width. Forms are printed, so many forms per page, with any specified number of blank lines between forms.

Once a format file has been created, a print format can be entered or modified using the standard commands already described (ADD, DELETE, and UPDATE). The first line of the print format is a 'header' line, and should consist of three numbers separated by commas:

Each of the following lines is equivalent to one line on the print form, and will contain one or more of the following:

In use these elements are separated by the 'bar' character '|'. The 'bar' character is specified twice in line 7080 and may be altered to any other character if you wish. To avoid future confusion you are recommended to decide on one particular character and stay with this. Further, each format line may be terminated by /R (for right justification) or /C for centring. Some examples are contained on the associated disc and described here separately.

ERROR MESSAGES

A considerable amount of checking and cross checking is incorporated in the program. The Filer error messages are largely self explanatory, and are listed below together with the commands to which they apply.

FILER SORT PROGRAM

This program provides a fast and efficient way of re-ordering any Filer database file. Unlike the other Filer programs, this works in mode 7 (for maximum memory space on a BBC micro model B), and is menu-driven, not command driven. You will be fully prompted for all the information required.

Initially you must specify the file to be sorted, and the name of the file to contain the sorted records. You then need to build up a 'sort key' by specifying the fields and part-fields on which the sort will be based. Files can also be sorted into ascending or descending order, and, if the sort key is based on a single field only, you may choose a numeric or alpha sort.

Once all this information has been gathered, and displayed on the screen for confirmation, the sort will take only a few minutes at most. Escape will abort the sort process at any time and return you to the main menu. However, the new file, though not the original file, may be corrupted as a result.

USING THE SAMPLE DATA FILES

An example data file called NAMES is supplied on the disc, and contains a number of records consisting of names and addresses. Using Filer, you can experiment with the various commands. Use SHOW to see the file's structure, and DISPLAY to examine the records.

For example, with Filer running, type commands as follows (each command can be abbreviated to its first two letters), and press Return after typing in each one:

to display each record in turn - press any key to move to the next record.

Three format files are also provided to illustrate the use of this feature. The format F.PHONE will produce a list of names and telephone numbers, F.LIST1 will list the names and addresses, one per line, while F.LIST2 will print the addresses as might be required for address labels. Continuing from the above example:

If you want to see what any of the format files looks like then examine these as data files using the OPEN and DISPLAY commands. Since Filer uses the full stop '.' as a pad character in records, this character cannot appear as the last character of any data item.

The PRINT and MERGE commands are designed to output to a printer. If you do not have a printer available, entering *FX5,0 will cause all printer output to be directed to the screen only. This is also useful for testing out formats without wasting paper. *FX5,1 (parallel printer) or *FX5,2 (serial printer) will restore output to the printer proper.

SETTING UP YOUR OWN FILER DISCS

You are recommended to set up one or more Filer discs of your own when you start to create data files. To do this copy the following files: Note that on a BBC micro, the value of PAGE must be set to &1400 before the program is loaded to give sufficient memory space for the Filer program to run.